package com.yangwudongxi.sqlloghelper.service;

import com.yangwudongxi.sqlloghelper.dto.DatabaseConfig;
import com.yangwudongxi.sqlloghelper.dto.ExecutionPlanStep;
import com.yangwudongxi.sqlloghelper.dto.QueryResult;
import com.yangwudongxi.sqlloghelper.dto.QueryResult.ColumnInfo;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 数据库查询服务
 *
 * @author 氧屋东西
 */
@Slf4j
@Service
public class DatabaseQueryService {
    
    /**
     * 最大查询结果数
     */
    private static final int MAX_ROWS = 100;
    
    /**
     * 数据源缓存（避免频繁创建连接）
     */
    private final Map<String, HikariDataSource> dataSourceCache = new ConcurrentHashMap<>();
    
    /**
     * 执行SQL查询
     */
    public QueryResult executeQuery(String sql, DatabaseConfig dbConfig) throws SQLException {
        // 验证SQL是否为SELECT语句
        if (!isSelectQuery(sql)) {
            throw new RuntimeException("只支持SELECT查询语句");
        }
        
        HikariDataSource dataSource = getOrCreateDataSource(dbConfig);
        
        try (Connection connection = dataSource.getConnection()) {
            // 执行查询
            return doExecuteQuery(connection, sql, dbConfig.getDatabase());
        }
    }
    
    /**
     * 测试数据库连接
     */
    public boolean testConnection(DatabaseConfig dbConfig) {
        HikariDataSource dataSource = null;
        try {
            dataSource = createDataSource(dbConfig);
            try (Connection connection = dataSource.getConnection()) {
                return connection.isValid(5);
            }
        } catch (Exception e) {
            log.error("数据库连接测试失败", e);
            return false;
        } finally {
            if (dataSource != null && !dataSource.isClosed()) {
                dataSource.close();
            }
        }
    }
    
    /**
     * 执行查询的核心方法
     */
    private QueryResult doExecuteQuery(Connection connection, String sql, String database) throws SQLException {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        
        try {
            // 限制查询结果数
            String limitedSql = addLimitToSql(sql);
            
            statement = connection.prepareStatement(limitedSql);
            statement.setMaxRows(MAX_ROWS + 1); // 多查一条用于判断是否有更多数据
            
            resultSet = statement.executeQuery();
            
            // 获取列信息
            List<ColumnInfo> columns = getColumnInfo(resultSet.getMetaData(), connection, database);
            
            // 获取数据行
            List<Map<String, Object>> rows = new ArrayList<>();
            int rowCount = 0;
            
            while (resultSet.next() && rowCount < MAX_ROWS) {
                Map<String, Object> row = new LinkedHashMap<>();
                for (ColumnInfo column : columns) {
                    Object value = resultSet.getObject(column.getName());
                    row.put(column.getName(), value);
                }
                rows.add(row);
                rowCount++;
            }
            
            // 判断是否有更多数据
            boolean hasMore = resultSet.next();
            
            return QueryResult.builder()
                    .columns(columns)
                    .rows(rows)
                    .total(rows.size())
                    .hasMore(hasMore)
                    .build();
                    
        } finally {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
        }
    }
    
    /**
     * 获取列信息（包括注释）
     */
    private List<ColumnInfo> getColumnInfo(ResultSetMetaData metaData, Connection connection, String database) throws SQLException {
        List<ColumnInfo> columns = new ArrayList<>();
        int columnCount = metaData.getColumnCount();
        
        // 获取表名和列注释的映射
        Map<String, Map<String, String>> tableColumnComments = new HashMap<>();
        
        for (int i = 1; i <= columnCount; i++) {
            String columnName = metaData.getColumnName(i);
            String tableName = metaData.getTableName(i);
            String dataType = metaData.getColumnTypeName(i);
            
            // 获取列注释
            String comment = null;
            if (tableName != null && !tableName.isEmpty() && database != null && !database.isEmpty()) {
                Map<String, String> columnComments = tableColumnComments.get(tableName);
                if (columnComments == null) {
                    columnComments = getTableColumnComments(connection, database, tableName);
                    tableColumnComments.put(tableName, columnComments);
                }
                comment = columnComments.get(columnName);
            }
            
            columns.add(ColumnInfo.builder()
                    .name(columnName)
                    .comment(comment)
                    .dataType(dataType)
                    .build());
        }
        
        return columns;
    }
    
    /**
     * 获取表的列注释
     */
    private Map<String, String> getTableColumnComments(Connection connection, String database, String tableName) {
        Map<String, String> comments = new HashMap<>();
        
        String sql = "SELECT COLUMN_NAME, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS " +
                    "WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
        
        try (PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, database);
            statement.setString(2, tableName);
            
            try (ResultSet resultSet = statement.executeQuery()) {
                while (resultSet.next()) {
                    String columnName = resultSet.getString("COLUMN_NAME");
                    String comment = resultSet.getString("COLUMN_COMMENT");
                    if (comment != null && !comment.isEmpty()) {
                        comments.put(columnName, comment);
                    }
                }
            }
        } catch (SQLException e) {
            log.warn("获取列注释失败", e);
        }
        
        return comments;
    }
    
    /**
     * 为SQL添加LIMIT限制
     */
    private String addLimitToSql(String sql) {
        String upperSql = sql.trim().toUpperCase();
        
        // 如果已经包含LIMIT，则不添加
        if (upperSql.contains(" LIMIT ")) {
            return sql;
        }
        
        // 添加LIMIT
        return sql + " LIMIT " + (MAX_ROWS + 1);
    }
    
    /**
     * 判断是否为SELECT查询
     */
    private boolean isSelectQuery(String sql) {
        String trimmedSql = sql.trim().toUpperCase();
        return trimmedSql.startsWith("SELECT");
    }
    
    /**
     * 获取或创建数据源
     */
    private HikariDataSource getOrCreateDataSource(DatabaseConfig dbConfig) {
        String key = generateDataSourceKey(dbConfig);
        
        log.debug("获取数据源，缓存键: {}, 配置: {}:{}/{}", 
                 key, dbConfig.getHost(), dbConfig.getPort(), dbConfig.getDatabase());
        
        return dataSourceCache.computeIfAbsent(key, k -> {
            try {
                log.info("创建新的数据源: {}:{}/{}, 用户: {}", 
                        dbConfig.getHost(), dbConfig.getPort(), dbConfig.getDatabase(), dbConfig.getUsername());
                return createDataSource(dbConfig);
            } catch (Exception e) {
                log.error("创建数据源失败，配置: {}:{}/{}, 用户: {}, 错误: {}", 
                         dbConfig.getHost(), dbConfig.getPort(), dbConfig.getDatabase(), 
                         dbConfig.getUsername(), e.getMessage());
                throw new RuntimeException("创建数据源失败：" + e.getMessage(), e);
            }
        });
    }
    
    /**
     * 创建数据源
     */
    private HikariDataSource createDataSource(DatabaseConfig dbConfig) {
        String jdbcUrl = getJdbcUrl(dbConfig.getHost(), dbConfig.getPort(), dbConfig.getDatabase());
        
        log.info("创建数据源 - JDBC URL: {}, 用户名: {}", jdbcUrl, dbConfig.getUsername());
        
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(jdbcUrl);
        config.setUsername(dbConfig.getUsername());
        config.setPassword(dbConfig.getPassword());
        config.setDriverClassName("com.mysql.cj.jdbc.Driver");
        
        // 连接池配置
        config.setMaximumPoolSize(5);
        config.setMinimumIdle(1);
        config.setIdleTimeout(300000);
        config.setConnectionTimeout(30000);
        config.setMaxLifetime(1800000);
        
        // 添加连接验证
        config.setConnectionTestQuery("SELECT 1");
        config.setValidationTimeout(5000);
        
        return new HikariDataSource(config);
    }
    
    /**
     * 生成数据源缓存键
     */
    private String generateDataSourceKey(DatabaseConfig dbConfig) {
        // 生成配置的哈希值，避免密码明文出现在键中
        String configStr = dbConfig.getHost() + ":" + dbConfig.getPort() + ":" + 
                          dbConfig.getUsername() + ":" + dbConfig.getDatabase() + ":" + 
                          dbConfig.getPassword();
        return String.valueOf(configStr.hashCode());
    }
    
    /**
     * 清理数据源缓存（可定期调用）
     */
    public void cleanupDataSources() {
        dataSourceCache.values().forEach(dataSource -> {
            if (!dataSource.isClosed()) {
                dataSource.close();
            }
        });
        dataSourceCache.clear();
    }

    /**
     * 获取SQL执行计划
     */
    public List<ExecutionPlanStep> getExecutionPlan(String sql, DatabaseConfig dbConfig) throws SQLException {
        // 验证SQL是否为SELECT语句
        if (!isSelectQuery(sql)) {
            throw new RuntimeException("只支持SELECT查询语句的执行计划分析");
        }
        
        HikariDataSource dataSource = getOrCreateDataSource(dbConfig);
        
        try (Connection connection = dataSource.getConnection()) {
            return doGetExecutionPlan(connection, sql);
        }
    }
    
    /**
     * 执行获取执行计划的核心方法
     */
    private List<ExecutionPlanStep> doGetExecutionPlan(Connection connection, String sql) throws SQLException {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        
        try {
            // 执行EXPLAIN语句
            String explainSql = "EXPLAIN " + sql;
            statement = connection.prepareStatement(explainSql);
            resultSet = statement.executeQuery();
            
            List<ExecutionPlanStep> steps = new ArrayList<>();
            
            while (resultSet.next()) {
                ExecutionPlanStep step = ExecutionPlanStep.builder()
                        .id(resultSet.getObject("id") != null ? resultSet.getInt("id") : null)
                        .selectType(resultSet.getString("select_type"))
                        .table(resultSet.getString("table"))
                        .partitions(resultSet.getString("partitions"))
                        .type(resultSet.getString("type"))
                        .possibleKeys(resultSet.getString("possible_keys"))
                        .key(resultSet.getString("key"))
                        .keyLen(resultSet.getString("key_len"))
                        .ref(resultSet.getString("ref"))
                        .rows(resultSet.getObject("rows") != null ? resultSet.getLong("rows") : null)
                        .filtered(resultSet.getObject("filtered") != null ? resultSet.getDouble("filtered") : null)
                        .extra(resultSet.getString("Extra"))
                        .build();
                        
                steps.add(step);
            }
            
            return steps;
            
        } finally {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
        }
    }

    /**
     * 从SQL语句中提取表名
     */
    public List<String> extractTableNames(String sql) {
        Set<String> tableNames = new HashSet<>();
        
        try {
            // 清理SQL语句
            String cleanSql = sql.replaceAll("\\s+", " ").trim();
            
            // FROM子句表名提取
            Pattern fromPattern = Pattern.compile("(?i)\\bFROM\\s+([\\w`]+)(?:\\s+(?:AS\\s+)?[\\w`]+)?", Pattern.CASE_INSENSITIVE);
            Matcher fromMatcher = fromPattern.matcher(cleanSql);
            while (fromMatcher.find()) {
                String tableName = fromMatcher.group(1).replaceAll("`", "");
                tableNames.add(tableName);
            }
            
            // JOIN子句表名提取
            Pattern joinPattern = Pattern.compile("(?i)\\b(?:INNER\\s+|LEFT\\s+|RIGHT\\s+|FULL\\s+)?JOIN\\s+([\\w`]+)(?:\\s+(?:AS\\s+)?[\\w`]+)?", Pattern.CASE_INSENSITIVE);
            Matcher joinMatcher = joinPattern.matcher(cleanSql);
            while (joinMatcher.find()) {
                String tableName = joinMatcher.group(1).replaceAll("`", "");
                tableNames.add(tableName);
            }
            
            // UPDATE语句表名提取
            Pattern updatePattern = Pattern.compile("(?i)\\bUPDATE\\s+([\\w`]+)", Pattern.CASE_INSENSITIVE);
            Matcher updateMatcher = updatePattern.matcher(cleanSql);
            while (updateMatcher.find()) {
                String tableName = updateMatcher.group(1).replaceAll("`", "");
                tableNames.add(tableName);
            }
            
            // INSERT语句表名提取
            Pattern insertPattern = Pattern.compile("(?i)\\bINSERT\\s+INTO\\s+([\\w`]+)", Pattern.CASE_INSENSITIVE);
            Matcher insertMatcher = insertPattern.matcher(cleanSql);
            while (insertMatcher.find()) {
                String tableName = insertMatcher.group(1).replaceAll("`", "");
                tableNames.add(tableName);
            }
            
        } catch (Exception e) {
            log.warn("表名提取失败，SQL: {}", sql, e);
        }
        
        return new ArrayList<>(tableNames);
    }
    
    /**
     * 获取指定表的建表语句
     */
    public String getTableCreateStatement(Connection connection, String database, String tableName) {
        String sql = "SHOW CREATE TABLE `" + database + "`.`" + tableName + "`";
        
        try (PreparedStatement statement = connection.prepareStatement(sql);
             ResultSet resultSet = statement.executeQuery()) {
            
            if (resultSet.next()) {
                return resultSet.getString(2); // 第二列是CREATE TABLE语句
            }
            
        } catch (SQLException e) {
            log.warn("获取表{}的建表语句失败", tableName, e);
        }
        
        return null;
    }
    
    /**
     * 获取SQL语句涉及的所有表的建表语句
     */
    public Map<String, String> getTablesCreateStatements(String sql, DatabaseConfig dbConfig) throws SQLException {
        Map<String, String> createStatements = new HashMap<>();
        
        // 提取表名
        List<String> tableNames = extractTableNames(sql);
        if (tableNames.isEmpty()) {
            log.warn("未能从SQL中提取到表名: {}", sql);
            return createStatements;
        }
        
        HikariDataSource dataSource = getOrCreateDataSource(dbConfig);
        
        try (Connection connection = dataSource.getConnection()) {
            for (String tableName : tableNames) {
                String createStatement = getTableCreateStatement(connection, dbConfig.getDatabase(), tableName);
                if (createStatement != null) {
                    createStatements.put(tableName, createStatement);
                    log.debug("获取到表{}的建表语句", tableName);
                } else {
                    log.warn("无法获取表{}的建表语句", tableName);
                }
            }
        }
        
        return createStatements;
    }

    public String getJdbcUrl(String host,int port,String database) {
        StringBuilder url = new StringBuilder("jdbc:mysql://");
        url.append(host).append(":").append(port);

        if (database != null && !database.trim().isEmpty()) {
            url.append("/").append(database);
        }

        url.append("?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai");

        return url.toString();
    }
    
    /**
     * 获取表注释信息
     */
    public Map<String, String> getTableComments(List<String> tableNames, DatabaseConfig dbConfig) throws SQLException {
        if (tableNames == null || tableNames.isEmpty()) {
            return new HashMap<>();
        }
        
        HikariDataSource dataSource = getOrCreateDataSource(dbConfig);
        Map<String, String> tableComments = new HashMap<>();
        
        try (Connection connection = dataSource.getConnection()) {
            // 构建查询表注释的SQL
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES ");
            sql.append("WHERE TABLE_SCHEMA = ? AND TABLE_NAME IN (");
            
            // 添加占位符
            for (int i = 0; i < tableNames.size(); i++) {
                sql.append("?");
                if (i < tableNames.size() - 1) {
                    sql.append(", ");
                }
            }
            sql.append(")");
            
            try (PreparedStatement statement = connection.prepareStatement(sql.toString())) {
                // 设置参数
                statement.setString(1, dbConfig.getDatabase());
                for (int i = 0; i < tableNames.size(); i++) {
                    statement.setString(i + 2, tableNames.get(i));
                }
                
                try (ResultSet resultSet = statement.executeQuery()) {
                    while (resultSet.next()) {
                        String tableName = resultSet.getString("TABLE_NAME");
                        String tableComment = resultSet.getString("TABLE_COMMENT");
                        
                        // 处理空注释
                        if (tableComment == null || tableComment.trim().isEmpty()) {
                            tableComment = "";
                        }
                        
                        tableComments.put(tableName, tableComment);
                    }
                }
            }
            
            // 为没有查询到的表添加空注释
            for (String tableName : tableNames) {
                if (!tableComments.containsKey(tableName)) {
                    tableComments.put(tableName, "");
                }
            }
            
            log.info("成功获取{}个表的注释信息", tableComments.size());
            return tableComments;
        }
    }
}